package com.taxipark.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.taxipark.domain.TaxiDriver;
import com.taxipark.domain.TaxiManager;
import com.taxipark.service.connection.ConnectionProvider;

public class DbSimpleReestr implements Reestr {
	private final ConnectionProvider connectionProvider;

	public DbSimpleReestr(ConnectionProvider connectionProvider) {
		this.connectionProvider = connectionProvider;
	}

	@Override
	public void save(TaxiDriver driver) {
		Connection conn = null;
		Statement s = null;
		TaxiManager manager = driver.getTaxiManager();
		try {
			conn = connectionProvider.provide();
			s = conn.createStatement();
			conn.setAutoCommit(false);

			if (manager.getId() <= 0) {

				String nameManager = manager.getName();
				String contactManager = manager.getContact();
				String query = "INSERT INTO managers(name, contact) VALUES('" + nameManager + "','" + contactManager + "')";
				s = conn.prepareStatement("INSERT INTO managers(name, contact) VALUES(?,?)");
				s.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = s.getGeneratedKeys();
				if (rs.next()) {
					manager.setId(rs.getInt(1));
				}
			}
			int managerId = driver.getManagerId();
			String name = driver.getName();
			String contact = driver.getContact();
			boolean activity = driver.getActivity();
			String query = "INSERT INTO drivers(manager_id, name, contact, activity) VALUES('" + managerId + "','" + name + "','" + contact + "'," + activity
					+ ")";
			s = conn.prepareStatement("INSERT INTO drivers(id, name, contact, activity) VALUES(?,?,?,?)");
			s.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs1 = s.getGeneratedKeys();
			if (rs1.next()) {
				driver.setId(rs1.getInt(1));
			}
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e2) {
			}
		} finally {
			try {
				s.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public List<TaxiDriver> getAll() {
		List<TaxiDriver> list = new ArrayList<TaxiDriver>();
		Connection conn = null;
		Statement s = null;
		try {
			conn = connectionProvider.provide();
			conn.setAutoCommit(false);
			s = conn.createStatement();
			String query = "SELECT * FROM drivers";
			ResultSet rs = s.executeQuery(query);

			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String contact = rs.getString("contact");
				boolean act = rs.getBoolean("activity");
				TaxiDriver driver = new TaxiDriver(name, contact);
				driver.setId(id);
				driver.setActivity(act);
				list.add(driver);
			}
				conn.commit();
				conn.setAutoCommit(true);
			

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e2) {
			}
		} finally {
			try {
				s.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return list;
	}

	@Override
	public void delete(TaxiDriver driver) {
		// String name = driver.getName();

		Connection conn = null;
		Statement s = null;

		try {
			conn = connectionProvider.provide();

			conn.setAutoCommit(false);

			s = conn.createStatement();

			String query = "DELETE FROM drivers WHERE id=" + driver.getId();
			s.executeUpdate(query);

			// ---->>>

			// delewtiopn ...

			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e2) {
			}
		} finally {
			try {
				s.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

}
